"""
Statistiques Accès Libre via l'API DATAtourisme
Avec gestion de l'historique cumulatif sur Google Drive
"""

# Créé par Anjou Tourisme Attractivité
# Annabelle ALLARD — annabelle-allard@anjou-tourisme.com

import requests
import time
import re
import os
import openpyxl
from openpyxl.chart import BarChart, LineChart, Reference
from openpyxl.chart.legend import Legend
from openpyxl.styles import PatternFill, Font, Alignment, Border, Side
from openpyxl.utils import get_column_letter
from collections import Counter
from datetime import date

# ── Montage Google Drive ──────────────────────────────────────────────────────
from google.colab import drive
drive.mount('/content/drive')

# ══════════════════════════════════════════════════════════════
# ⚙️  CONFIGURATION — champs à personnaliser avant de lancer
# ══════════════════════════════════════════════════════════════

# 🔑 Votre clé API DATAtourisme
API_KEY = "VOTRE-CLÉ-API-ICI"

# 🗺️  Filtre géographique
# Pour un département  : insee[eq]=XX
# Pour plusieurs       : insee[in]=XX,YY,ZZ
FILTER_EXPR = (
    "hasExternalReference.hasExternalPlatform.hasExternalPlatformUrl[eq]=https://acceslibre.beta.gouv.fr"
    " AND "
    "isLocatedAt.address.hasAddressCity.isPartOfDepartment.insee[eq]=XX"
)

# 📂 Dossier Google Drive où stocker le fichier historique
# Exemple : "/content/drive/My Drive/Accès Libre/Stats"
DOSSIER_DRIVE = "/content/drive/My Drive"

# ══════════════════════════════════════════════════════════════
# ⛔  Ne pas modifier en dessous de cette ligne
# ══════════════════════════════════════════════════════════════

BASE_URL  = "https://api.datatourisme.fr/v1"
PAGE_SIZE = 250
PAUSE     = 0.15

DATE_PREFIX = date.today().strftime("%Y%m%d")
DATE_STR    = date.today().strftime("%d/%m/%Y")

match = re.search(r"isPartOfDepartment\.insee\[(?:eq|in)\]=([0-9,]+)", FILTER_EXPR)
ZONE = match.group(1).replace(",", "-") if match else "inconnu"

OUTPUT_STATS      = f"{DATE_PREFIX}-AccesLibre-stats-{ZONE}.xlsx"
OUTPUT_HISTORIQUE = os.path.join(DOSSIER_DRIVE, f"historique_acceslibre_{ZONE}.xlsx")

FIELDS_ACCESLIBRE = ",".join([
    "identifier",
    "hasExternalReference",
    "hasBeenCreatedBy",
    "isLocatedAt.address.hasAddressCity",
    "isLocatedAt.address.hasAddressCity.isPartOfDepartment",
])

match_total = re.search(r"(isLocatedAt\.address\.hasAddressCity\.isPartOfDepartment\.insee\[(?:eq|in)\]=[0-9,]+)", FILTER_EXPR)
FILTER_TOTAL = match_total.group(1) if match_total else ""

HEADERS_API = {"X-API-Key": API_KEY, "Accept": "application/json"}

DEPT_LABELS = {
    "44": "Loire-Atlantique", "49": "Maine-et-Loire", "53": "Mayenne",
    "72": "Sarthe", "85": "Vendée"
}

TYPES_IDENTIFIER = {
    "ACCAM": "Aires de camping-car",
    "ASC":   "Activités sportives et de loisirs",
    "FMA":   "Fêtes et Manifestations",
    "HCO":   "Hébergements collectifs",
    "HLO":   "Hébergements locatifs",
    "HOT":   "Hôtellerie",
    "LOI":   "Loisirs",
    "ORG":   "Organismes",
    "PCU":   "Patrimoine culturel",
    "PNA":   "Patrimoine Naturel",
    "RES":   "Restauration",
    "RET":   "Résidences de tourisme",
    "VIL":   "Villages Vacances",
}

# ── Palette Anjou Tourisme ────────────────────────────────────────────────────
VERT_FONCE  = "1B6F5C"
VERT_MOYEN  = "49B170"
VERT_CLAIR  = "E8F5EE"
ORANGE      = "F7AB59"
BLANC       = "FFFFFF"
TEXTE       = "212121"
GRIS_TEXTE  = "5A5A5A"
POLICE      = "Century Gothic"

# ── Helpers styles ────────────────────────────────────────────────────────────
def fill(hex_color):
    return PatternFill("solid", fgColor=hex_color)

def font(bold=False, color=TEXTE, size=10, italic=False):
    return Font(bold=bold, color=color, size=size, name=POLICE, italic=italic)

def border_thin():
    s = Side(style="thin", color="CCCCCC")
    return Border(left=s, right=s, top=s, bottom=s)

def border_bottom(color=VERT_MOYEN):
    s = Side(style="medium", color=color)
    return Border(bottom=s)

def align(h="left", v="center", wrap=False):
    return Alignment(horizontal=h, vertical=v, wrap_text=wrap)

def style_header(cell):
    cell.fill = fill(VERT_FONCE)
    cell.font = font(bold=True, color=BLANC)
    cell.alignment = align("center")
    cell.border = border_thin()

def style_cell(cell, bg=BLANC, bold=False, color=TEXTE, h="left"):
    cell.fill = fill(bg)
    cell.font = font(bold=bold, color=color)
    cell.alignment = align(h)
    cell.border = border_thin()

def set_col_width(ws, col, width):
    ws.column_dimensions[get_column_letter(col)].width = width

def add_bar_chart(ws, title, legend_label, data_ref, cat_ref, anchor, color=VERT_MOYEN):
    chart = BarChart()
    chart.type = "bar"
    chart.title = title
    chart.style = 10
    chart.width = 20
    chart.height = 14
    chart.y_axis.title = "Nombre de POI"
    chart.x_axis.title = legend_label
    data = Reference(ws, min_col=data_ref[0], min_row=data_ref[1], max_row=data_ref[2])
    cats = Reference(ws, min_col=cat_ref[0],  min_row=cat_ref[1],  max_row=cat_ref[2])
    chart.add_data(data)
    chart.set_categories(cats)
    chart.series[0].graphicalProperties.solidFill = color
    chart.series[0].graphicalProperties.line.solidFill = color
    chart.legend = Legend()
    chart.legend.position = "b"
    ws.add_chart(chart, anchor)

def add_line_chart(ws, title, cols_data, col_cats, min_row, max_row, anchor, colors=None):
    chart = LineChart()
    chart.title = title
    chart.style = 10
    chart.width = 22
    chart.height = 14
    chart.y_axis.title = "Nombre de POI"
    chart.x_axis.title = "Date"
    for i, col in enumerate(cols_data):
        data = Reference(ws, min_col=col, min_row=min_row - 1, max_row=max_row)
        chart.add_data(data, titles_from_data=True)
        if colors and i < len(colors):
            chart.series[i].graphicalProperties.line.solidFill = colors[i]
            chart.series[i].graphicalProperties.line.width = 20000
    cats = Reference(ws, min_col=col_cats, min_row=min_row, max_row=max_row)
    chart.set_categories(cats)
    chart.legend = Legend()
    chart.legend.position = "b"
    ws.add_chart(chart, anchor)

def titre_onglet(ws, text):
    ws["B2"] = text
    ws["B2"].font = Font(bold=True, color=VERT_FONCE, size=14, name=POLICE)
    ws["B2"].alignment = align("left", "center")
    ws.row_dimensions[2].height = 28
    for col in range(2, 6):
        ws.cell(row=3, column=col).border = border_bottom(VERT_MOYEN)

def make_total_row(ws, row, col_label, col_val, total):
    ws.row_dimensions[row].height = 20
    c = ws.cell(row=row, column=col_label, value="TOTAL")
    style_cell(c, bg=VERT_FONCE, bold=True, color=BLANC, h="right")
    c = ws.cell(row=row, column=col_val, value=total)
    style_cell(c, bg=VERT_FONCE, bold=True, color=BLANC, h="center")

# ── Helpers extraction ────────────────────────────────────────────────────────
def get_type(identifier):
    for prefix in TYPES_IDENTIFIER:
        if identifier.upper().startswith(prefix):
            return prefix, TYPES_IDENTIFIER[prefix]
    return "AUTRES", "Préfixes non reconnus"

def extract_acceslibre_id(refs):
    if not isinstance(refs, list):
        refs = [refs]
    for ref in refs:
        if not isinstance(ref, dict):
            continue
        platform = ref.get("hasExternalPlatform", [])
        if isinstance(platform, list):
            platform = platform[0] if platform else {}
        url = platform.get("hasExternalPlatformUrl", "")
        if "acceslibre" in url.lower():
            return ref.get("hasExternalIdentifier")
    return None

def extract_commune(poi):
    try:
        locations = poi.get("isLocatedAt", [])
        if not isinstance(locations, list):
            locations = [locations]
        address = locations[0]["address"]
        if isinstance(address, list):
            address = address[0]
        city = address["hasAddressCity"]
        label = city.get("label", {})
        nom = label.get("@fr") or label.get("@en", "") if isinstance(label, dict) else str(label)
        insee = city.get("insee", "")
        return f"{nom} ({insee})" if nom else "Non renseignée"
    except (KeyError, IndexError, TypeError):
        return "Non renseignée"

def extract_departement(poi):
    try:
        locations = poi.get("isLocatedAt", [])
        if not isinstance(locations, list):
            locations = [locations]
        address = locations[0]["address"]
        if isinstance(address, list):
            address = address[0]
        dept = address["hasAddressCity"]["isPartOfDepartment"]
        if isinstance(dept, list):
            dept = dept[0] if dept else {}
        return dept.get("insee", "Non renseigné")
    except (KeyError, IndexError, TypeError):
        return "Non renseigné"

def extract_producteur(poi):
    try:
        created_by = poi.get("hasBeenCreatedBy", {})
        if isinstance(created_by, list):
            created_by = created_by[0] if created_by else {}
        return created_by.get("legalName", "") or "Non renseigné"
    except (KeyError, IndexError, TypeError):
        return "Non renseigné"

# ── Appels API ────────────────────────────────────────────────────────────────
def fetch_page(url, params=None):
    resp = requests.get(url, headers=HEADERS_API, params=params, timeout=30)
    resp.raise_for_status()
    return resp.json()

def fetch_total_datatourisme():
    params = {"api_key": API_KEY, "filters": FILTER_TOTAL, "page_size": 1, "page": 1}
    data = fetch_page(f"{BASE_URL}/catalog", params)
    return data.get("meta", {}).get("total", 0)

def fetch_all_acceslibre():
    params = {
        "api_key":   API_KEY,
        "fields":    FIELDS_ACCESLIBRE,
        "filters":   FILTER_EXPR,
        "page_size": PAGE_SIZE,
        "page":      1,
    }
    results  = []
    url      = f"{BASE_URL}/catalog"
    page_num = 0
    while url:
        page_num += 1
        print(f"  Page {page_num}…", end=" ", flush=True)
        data    = fetch_page(url, params if page_num == 1 else None)
        objects = data.get("objects", [])
        meta    = data.get("meta", {})
        if page_num == 1:
            print(f"(total : {meta.get('total', '?')} POIs, {meta.get('total_pages', '?')} pages)")
        for poi in objects:
            if extract_acceslibre_id(poi.get("hasExternalReference", [])):
                results.append({
                    "identifier":  poi.get("identifier", ""),
                    "commune":     extract_commune(poi),
                    "departement": extract_departement(poi),
                    "producteur":  extract_producteur(poi),
                })
        print(f"    → {len(results)} trouvés jusqu'ici")
        url = meta.get("next")
        if url:
            time.sleep(PAUSE)
    return results

# ── Historique ────────────────────────────────────────────────────────────────
def load_historique():
    if os.path.exists(OUTPUT_HISTORIQUE):
        wb = openpyxl.load_workbook(OUTPUT_HISTORIQUE)
        ws = wb["Historique brut"]
        rows = []
        for row in ws.iter_rows(min_row=6, min_col=2, values_only=True):
            if row[0]:
                rows.append(list(row))
        print(f"  → Historique chargé depuis Drive : {len(rows)} entrée(s) existante(s)")
        return rows
    print("  → Aucun historique existant sur Drive, création d'un nouveau fichier")
    return []

def build_historique_row(poi_list, total_datatourisme):
    total_al = len(poi_list)
    pct = round(total_al / total_datatourisme * 100, 1) if total_datatourisme else 0
    bordereau_counts = Counter()
    for poi in poi_list:
        prefix, _ = get_type(poi["identifier"])
        bordereau_counts[prefix] += 1
    prod_counts    = Counter(p["producteur"]  for p in poi_list)
    commune_counts = Counter(p["commune"]     for p in poi_list)
    dept_counts    = Counter(p["departement"] for p in poi_list)
    row = [
        DATE_STR,
        total_datatourisme,
        total_al,
        pct,
        len(prod_counts),
        len(commune_counts),
        len(dept_counts),
    ]
    for prefix in TYPES_IDENTIFIER:
        row.append(bordereau_counts.get(prefix, 0))
    return row

def save_historique(historique_rows):
    wb = openpyxl.Workbook()
    wb.remove(wb.active)

    headers_base = [
        "Date", "Total DATAtourisme", "Total Accès Libre", "Ratio (%)",
        "Nb producteurs", "Nb communes", "Nb départements",
    ]
    headers_bordereaux = list(TYPES_IDENTIFIER.keys())
    all_headers = headers_base + headers_bordereaux
    nb_rows = len(historique_rows)

    # Historique brut
    ws = wb.create_sheet("Historique brut")
    ws.sheet_view.showGridLines = False
    set_col_width(ws, 1, 4)
    set_col_width(ws, 2, 14)
    for i in range(3, len(all_headers) + 2):
        set_col_width(ws, i, 18)

    titre_onglet(ws, f"Historique complet — Zone {ZONE}")
    for i, h in enumerate(all_headers, 2):
        style_header(ws.cell(row=5, column=i, value=h))

    for ri, row in enumerate(historique_rows):
        r = 6 + ri
        ws.row_dimensions[r].height = 18
        bg = VERT_CLAIR if ri % 2 == 0 else BLANC
        for ci, val in enumerate(row, 2):
            c = ws.cell(row=r, column=ci, value=val)
            style_cell(c, bg=bg, h="center" if ci > 2 else "left")

    # Évolution globale
    ws2 = wb.create_sheet("Évolution globale")
    ws2.sheet_view.showGridLines = False
    set_col_width(ws2, 1, 4)
    set_col_width(ws2, 2, 14)
    set_col_width(ws2, 3, 20)
    set_col_width(ws2, 4, 20)
    set_col_width(ws2, 5, 16)

    titre_onglet(ws2, "Évolution globale dans le temps")
    for i, h in enumerate(["Date", "Total DATAtourisme", "Total Accès Libre", "Ratio (%)"], 2):
        style_header(ws2.cell(row=5, column=i, value=h))

    for ri, row in enumerate(historique_rows):
        r = 6 + ri
        ws2.row_dimensions[r].height = 18
        bg = VERT_CLAIR if ri % 2 == 0 else BLANC
        for ci, val in enumerate([row[0], row[1], row[2], row[3]], 2):
            c = ws2.cell(row=r, column=ci, value=val)
            style_cell(c, bg=bg, h="center" if ci > 2 else "left")

    if nb_rows >= 2:
        add_line_chart(ws2, "Évolution du nombre de POI",
                       [3, 4], 2, 6, 5 + nb_rows, "G5",
                       [VERT_FONCE, VERT_MOYEN])
        add_line_chart(ws2, "Évolution du ratio Accès Libre / DATAtourisme (%)",
                       [5], 2, 6, 5 + nb_rows, "G22",
                       [ORANGE])
    else:
        c = ws2.cell(row=8, column=7,
                     value="⚠ Au moins 2 entrées sont nécessaires pour afficher les graphiques.")
        c.font = font(italic=True, color=GRIS_TEXTE)

    # Évolution par bordereau
    ws3 = wb.create_sheet("Évolution par bordereau")
    ws3.sheet_view.showGridLines = False
    set_col_width(ws3, 1, 4)
    set_col_width(ws3, 2, 14)
    for i in range(3, len(TYPES_IDENTIFIER) + 3):
        set_col_width(ws3, i, 14)

    titre_onglet(ws3, "Évolution par type d'offre (bordereau)")
    headers3 = ["Date"] + list(TYPES_IDENTIFIER.values())
    for i, h in enumerate(headers3, 2):
        style_header(ws3.cell(row=5, column=i, value=h))

    for ri, row in enumerate(historique_rows):
        r = 6 + ri
        ws3.row_dimensions[r].height = 18
        bg = VERT_CLAIR if ri % 2 == 0 else BLANC
        c = ws3.cell(row=r, column=2, value=row[0])
        style_cell(c, bg=bg)
        for ci, val in enumerate(row[7:7 + len(TYPES_IDENTIFIER)], 3):
            c = ws3.cell(row=r, column=ci, value=val)
            style_cell(c, bg=bg, h="center")

    if nb_rows >= 2:
        add_line_chart(ws3, "Évolution du nombre de POI par type d'offre",
                       list(range(3, 3 + len(TYPES_IDENTIFIER))),
                       2, 6, 5 + nb_rows,
                       "B" + str(8 + nb_rows),
                       [VERT_FONCE, VERT_MOYEN, ORANGE, "EB5D48", "213A8F",
                        "ED6EA7", "5C9BD6", "70AD47", "FFC000", "7030A0",
                        "00B0F0", "FF0000", "C55A11"])
    else:
        c = ws3.cell(row=8, column=2,
                     value="⚠ Au moins 2 entrées sont nécessaires pour afficher les graphiques.")
        c.font = font(italic=True, color=GRIS_TEXTE)

    wb.properties.creator = "Annabelle ALLARD"
    wb.properties.company = "Anjou Tourisme Attractivité"
    wb.properties.description = "Historique Accès Libre × DATAtourisme — annabelle-allard@anjou-tourisme.com"

    wb.save(OUTPUT_HISTORIQUE)
    print(f"✓ Historique : {OUTPUT_HISTORIQUE} ({nb_rows} entrée(s))")

# ── Stats du jour ─────────────────────────────────────────────────────────────
def save_stats(poi_list, total_datatourisme, historique_rows):
    wb = openpyxl.Workbook()
    wb.remove(wb.active)

    total_al = len(poi_list)
    pct = round(total_al / total_datatourisme * 100, 1) if total_datatourisme else 0
    nb_rows = len(historique_rows)

    bordereau_counts = Counter()
    for poi in poi_list:
        prefix, _ = get_type(poi["identifier"])
        bordereau_counts[prefix] += 1

    prod_counts    = Counter(p["producteur"]  for p in poi_list)
    commune_counts = Counter(p["commune"]     for p in poi_list)
    dept_counts    = Counter(p["departement"] for p in poi_list)

    # ── Synthèse ──────────────────────────────────────────────────────────────
    ws = wb.create_sheet("Synthèse")
    ws.sheet_view.showGridLines = False
    set_col_width(ws, 1, 4)
    set_col_width(ws, 2, 40)
    set_col_width(ws, 3, 24)

    ws["B2"] = f"Statistiques Accès Libre — Zone {ZONE}"
    ws["B2"].font = Font(bold=True, color=VERT_FONCE, size=18, name=POLICE)
    ws["B2"].alignment = align("left", "center")
    ws["C2"] = f"Généré le {DATE_STR}"
    ws["C2"].font = Font(color=GRIS_TEXTE, size=10, name=POLICE, italic=True)
    ws["C2"].alignment = align("right", "center")
    ws.row_dimensions[2].height = 34
    for col in range(2, 4):
        ws.cell(row=3, column=col).border = border_bottom(VERT_MOYEN)

    kpis = [
        ("Total POI sur DATAtourisme",       str(total_datatourisme),                     VERT_FONCE),
        ("POI référencés sur Accès Libre",   str(total_al),                               VERT_MOYEN),
        ("Ratio Accès Libre / DATAtourisme", f"{total_al}/{total_datatourisme} ({pct} %)", ORANGE),
        ("Nombre de producteurs",            str(len(prod_counts)),                        VERT_FONCE),
        ("Nombre de communes couvertes",     str(len(commune_counts)),                     VERT_MOYEN),
        ("Nombre de départements couverts",  str(len(dept_counts)),                        VERT_FONCE),
    ]

    for i, (label, value, color) in enumerate(kpis, start=5):
        ws.row_dimensions[i].height = 28
        c_label = ws.cell(row=i, column=2, value=label)
        c_label.font = font(size=11)
        c_label.alignment = align("left", "center")
        c_label.fill = fill(VERT_CLAIR if i % 2 == 0 else BLANC)
        c_label.border = border_thin()
        c_val = ws.cell(row=i, column=3, value=value)
        c_val.font = Font(bold=True, color=BLANC, size=12, name=POLICE)
        c_val.alignment = align("center", "center")
        c_val.fill = fill(color)
        c_val.border = border_thin()

    sep_row = 5 + len(kpis) + 1
    for col in range(2, 4):
        ws.cell(row=sep_row, column=col).border = border_bottom(VERT_MOYEN)
    zone_row = sep_row + 2
    ws.row_dimensions[zone_row].height = 22
    c_label = ws.cell(row=zone_row, column=2, value="Zone géographique analysée")
    c_label.font = font(bold=True, size=11, color=VERT_FONCE)
    c_label.alignment = align("left", "center")
    depts = ZONE.split("-")
    zone_libelle = " · ".join(f"{DEPT_LABELS.get(d, d)} ({d})" for d in depts)
    zone_row2 = zone_row + 1
    ws.row_dimensions[zone_row2].height = 24
    c_val = ws.cell(row=zone_row2, column=2, value=zone_libelle)
    c_val.font = Font(bold=True, color=BLANC, size=12, name=POLICE)
    c_val.alignment = align("center", "center")
    c_val.fill = fill(VERT_MOYEN)
    c_val.border = border_thin()
    ws.merge_cells(start_row=zone_row2, start_column=2, end_row=zone_row2, end_column=3)

    # ── Par commune ───────────────────────────────────────────────────────────
    ws4 = wb.create_sheet("Par commune")
    ws4.sheet_view.showGridLines = False
    set_col_width(ws4, 1, 4)
    set_col_width(ws4, 2, 38)
    set_col_width(ws4, 3, 18)
    titre_onglet(ws4, "Répartition par commune")
    for i, h in enumerate(["Commune (code INSEE)", "Nombre de POI"], 2):
        style_header(ws4.cell(row=5, column=i, value=h))
    rows4 = sorted(commune_counts.items(), key=lambda x: -x[1])
    for i, (commune, count) in enumerate(rows4):
        r = 6 + i
        ws4.row_dimensions[r].height = 18
        bg = VERT_CLAIR if i % 2 == 0 else BLANC
        style_cell(ws4.cell(row=r, column=2, value=commune), bg=bg)
        style_cell(ws4.cell(row=r, column=3, value=count), bg=bg, h="center")
    make_total_row(ws4, 6 + len(rows4), 2, 3, total_al)
    add_bar_chart(ws4, "Nombre de POI par commune", "Communes",
                  (3, 6, 5 + len(rows4)), (2, 6, 5 + len(rows4)), "E5", ORANGE)

    # ── Par territoire ────────────────────────────────────────────────────────
    ws5 = wb.create_sheet("Par territoire")
    ws5.sheet_view.showGridLines = False
    set_col_width(ws5, 1, 4)
    set_col_width(ws5, 2, 28)
    set_col_width(ws5, 3, 18)
    titre_onglet(ws5, "Répartition par territoire (département)")
    for i, h in enumerate(["Département", "Nombre de POI"], 2):
        style_header(ws5.cell(row=5, column=i, value=h))
    rows5 = sorted(dept_counts.items(), key=lambda x: -x[1])
    for i, (dept, count) in enumerate(rows5):
        r = 6 + i
        ws5.row_dimensions[r].height = 20
        bg = VERT_CLAIR if i % 2 == 0 else BLANC
        label = f"{DEPT_LABELS.get(dept, dept)} ({dept})"
        style_cell(ws5.cell(row=r, column=2, value=label), bg=bg)
        style_cell(ws5.cell(row=r, column=3, value=count), bg=bg, h="center")
    make_total_row(ws5, 6 + len(rows5), 2, 3, total_al)
    add_bar_chart(ws5, "Nombre de POI par territoire", "Départements",
                  (3, 6, 5 + len(rows5)), (2, 6, 5 + len(rows5)), "E5", VERT_FONCE)

    # ── Par producteur ────────────────────────────────────────────────────────
    ws3 = wb.create_sheet("Par producteur")
    ws3.sheet_view.showGridLines = False
    set_col_width(ws3, 1, 4)
    set_col_width(ws3, 2, 44)
    set_col_width(ws3, 3, 18)
    titre_onglet(ws3, "Répartition par producteur (office de tourisme)")
    for i, h in enumerate(["Producteur", "Nombre de POI"], 2):
        style_header(ws3.cell(row=5, column=i, value=h))
    rows3 = sorted(prod_counts.items(), key=lambda x: -x[1])
    for i, (prod, count) in enumerate(rows3):
        r = 6 + i
        ws3.row_dimensions[r].height = 18
        bg = VERT_CLAIR if i % 2 == 0 else BLANC
        style_cell(ws3.cell(row=r, column=2, value=prod), bg=bg)
        style_cell(ws3.cell(row=r, column=3, value=count), bg=bg, h="center")
    make_total_row(ws3, 6 + len(rows3), 2, 3, total_al)
    add_bar_chart(ws3, "Nombre de POI par producteur", "Producteurs",
                  (3, 6, 5 + len(rows3)), (2, 6, 5 + len(rows3)), "E5", VERT_FONCE)

    # ── Par bordereau Tourinsoft ───────────────────────────────────────────────
    ws2 = wb.create_sheet("Par bordereau Tourinsoft")
    ws2.sheet_view.showGridLines = False
    set_col_width(ws2, 1, 4)
    set_col_width(ws2, 2, 14)
    set_col_width(ws2, 3, 34)
    set_col_width(ws2, 4, 18)
    titre_onglet(ws2, "Répartition par type d'offre (bordereau Tourinsoft)")
    for i, h in enumerate(["Préfixe", "Catégorie", "Nombre de POI"], 2):
        style_header(ws2.cell(row=5, column=i, value=h))
    rows2 = sorted(
        [(p, TYPES_IDENTIFIER.get(p, "Préfixes non reconnus"), c)
         for p, c in bordereau_counts.items()],
        key=lambda x: -x[2]
    )
    for i, (prefix, label, count) in enumerate(rows2):
        r = 6 + i
        ws2.row_dimensions[r].height = 18
        bg = VERT_CLAIR if i % 2 == 0 else BLANC
        for col, val, h in [(2, prefix, "center"), (3, label, "left"), (4, count, "center")]:
            style_cell(ws2.cell(row=r, column=col, value=val), bg=bg, h=h)
    make_total_row(ws2, 6 + len(rows2), 3, 4, total_al)
    ws2.cell(row=6 + len(rows2), column=2).fill = fill(VERT_FONCE)
    add_bar_chart(ws2, "Nombre de POI par type d'offre", "Types d'offre",
                  (4, 6, 5 + len(rows2)), (3, 6, 5 + len(rows2)), "G5", VERT_MOYEN)

    # ── Évolution globale ─────────────────────────────────────────────────────
    ws_eg = wb.create_sheet("Évolution globale")
    ws_eg.sheet_view.showGridLines = False
    set_col_width(ws_eg, 1, 4)
    set_col_width(ws_eg, 2, 14)
    set_col_width(ws_eg, 3, 20)
    set_col_width(ws_eg, 4, 20)
    set_col_width(ws_eg, 5, 16)

    titre_onglet(ws_eg, "Évolution globale dans le temps")
    for i, h in enumerate(["Date", "Total DATAtourisme", "Total Accès Libre", "Ratio (%)"], 2):
        style_header(ws_eg.cell(row=5, column=i, value=h))

    for ri, row in enumerate(historique_rows):
        r = 6 + ri
        ws_eg.row_dimensions[r].height = 18
        bg = VERT_CLAIR if ri % 2 == 0 else BLANC
        for ci, val in enumerate([row[0], row[1], row[2], row[3]], 2):
            c = ws_eg.cell(row=r, column=ci, value=val)
            style_cell(c, bg=bg, h="center" if ci > 2 else "left")

    if nb_rows >= 2:
        add_line_chart(ws_eg, "Évolution du nombre de POI",
                       [3, 4], 2, 6, 5 + nb_rows, "G5",
                       [VERT_FONCE, VERT_MOYEN])
        add_line_chart(ws_eg, "Évolution du ratio Accès Libre / DATAtourisme (%)",
                       [5], 2, 6, 5 + nb_rows, "G22",
                       [ORANGE])
    else:
        c = ws_eg.cell(row=8, column=7,
                       value="⚠ Au moins 2 entrées sont nécessaires pour afficher les graphiques.")
        c.font = font(italic=True, color=GRIS_TEXTE)

    # ── Évolution par bordereau ───────────────────────────────────────────────
    ws_eb = wb.create_sheet("Évolution par bordereau")
    ws_eb.sheet_view.showGridLines = False
    set_col_width(ws_eb, 1, 4)
    set_col_width(ws_eb, 2, 14)
    for i in range(3, len(TYPES_IDENTIFIER) + 3):
        set_col_width(ws_eb, i, 14)

    titre_onglet(ws_eb, "Évolution par type d'offre (bordereau)")
    headers_eb = ["Date"] + list(TYPES_IDENTIFIER.values())
    for i, h in enumerate(headers_eb, 2):
        style_header(ws_eb.cell(row=5, column=i, value=h))

    for ri, row in enumerate(historique_rows):
        r = 6 + ri
        ws_eb.row_dimensions[r].height = 18
        bg = VERT_CLAIR if ri % 2 == 0 else BLANC
        c = ws_eb.cell(row=r, column=2, value=row[0])
        style_cell(c, bg=bg)
        for ci, val in enumerate(row[7:7 + len(TYPES_IDENTIFIER)], 3):
            c = ws_eb.cell(row=r, column=ci, value=val)
            style_cell(c, bg=bg, h="center")

    if nb_rows >= 2:
        add_line_chart(ws_eb, "Évolution du nombre de POI par type d'offre",
                       list(range(3, 3 + len(TYPES_IDENTIFIER))),
                       2, 6, 5 + nb_rows,
                       "B" + str(8 + nb_rows),
                       [VERT_FONCE, VERT_MOYEN, ORANGE, "EB5D48", "213A8F",
                        "ED6EA7", "5C9BD6", "70AD47", "FFC000", "7030A0",
                        "00B0F0", "FF0000", "C55A11"])
    else:
        c = ws_eb.cell(row=8, column=2,
                       value="⚠ Au moins 2 entrées sont nécessaires pour afficher les graphiques.")
        c.font = font(italic=True, color=GRIS_TEXTE)

    wb.properties.creator = "Annabelle ALLARD"
    wb.properties.company = "Anjou Tourisme Attractivité"
    wb.properties.description = "Export Accès Libre × DATAtourisme — annabelle-allard@anjou-tourisme.com"

    wb.save(OUTPUT_STATS)
    print(f"✓ Stats     : {OUTPUT_STATS} ({total_al} POIs analysés)")

# ── Main ──────────────────────────────────────────────────────────────────────
if __name__ == "__main__":
    print("=" * 60)
    print(f"Statistiques Accès Libre — zone : {ZONE}")
    print("Source : API DATAtourisme v1")
    print("=" * 60)

    try:
        print("\n1. Récupération du total DATAtourisme…")
        total_dt = fetch_total_datatourisme()
        print(f"   → {total_dt} POI(s) au total sur le territoire")

        print("\n2. Récupération des POI Accès Libre…")
        poi_list = fetch_all_acceslibre()

        print(f"\n{'='*60}")
        print(f"Résultat : {len(poi_list)} POI(s) avec un ID Accès Libre")
        print(f"Ratio    : {len(poi_list)}/{total_dt} ({round(len(poi_list)/total_dt*100,1) if total_dt else 0} %)")

        if poi_list:
            print("\n3. Chargement de l'historique…")
            historique = load_historique()
            historique_avec_aujourdhui = historique + [build_historique_row(poi_list, total_dt)]

            print("\n4. Génération des stats du jour…")
            save_stats(poi_list, total_dt, historique_avec_aujourdhui)

            print("\n5. Mise à jour de l'historique sur Google Drive…")
            save_historique(historique_avec_aujourdhui)

            try:
                from google.colab import files
                files.download(OUTPUT_STATS)
            except ImportError:
                pass
        else:
            print("\nAucun résultat.")

    except requests.HTTPError as e:
        print(f"\n✗ Erreur HTTP : {e}")
    except KeyboardInterrupt:
        print("\n✗ Interrompu.")